package dao;

import entity.Record;
import util.DBUtil;
import util.DateUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class RecordDAO {
    public void add(Record record) {
        String sql = "insert into record values(null,?,?,?,?)";
        try(Connection c = DBUtil.getConnection();
            PreparedStatement ps = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)){

            ps.setInt(1,record.spend);
            ps.setInt(2,record.cid);
            ps.setString(3,record.comment);
            ps.setDate(4,DateUtil.util2sql(record.date));
            ps.execute();

            ResultSet rs = ps.getGeneratedKeys();//给出自增长id，赋给config
            if(rs.next()) {
                int id = rs.getInt(1);
                record.setId(id);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void update(Record record) {
        String sql = "update record set spend=?,cid=?,comment=?,date=? where id=?";
        try(Connection c = DBUtil.getConnection();
            PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setInt(1,record.spend);
            ps.setInt(2,record.cid);
            ps.setString(3,record.comment);
            ps.setDate(4,DateUtil.util2sql(record.date));
            ps.setInt(5,record.id);

            ps.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void delete(int id) {

        try(Connection c = DBUtil.getConnection();
            Statement s = c.createStatement();) {

            String sql = "delete from record where id = " + id;
            s.execute(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Record get(int id) {
        Record record = null;
        String sql = "select * from category where id = "+id;

        try(Connection c = DBUtil.getConnection();
            Statement s = c.createStatement();) {

            ResultSet rs = s.executeQuery(sql);
            if(rs.next())
            {
                record = new Record();
                int spend = rs.getInt("spend");
                int cid = rs.getInt("cid");
                String comment = rs.getString("comment");
                Date date = rs.getDate("date");

                record.spend = spend;
                record.cid = cid;
                record.comment = comment;
                record.date = date;//java.sql.Date,java.sql.Time和java.sql.Timestamp三个都是java.util.Date的子类（包装类）。
                record.id = id;

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return record;
    }

    public List<Record> list() {

        return list(0,Short.MAX_VALUE);
    }

    public List<Record> list(int start, int count) {
        List<Record> configList = new ArrayList<Record>();

        String sql = "select * from record order by id desc limit ?,?";

        try(Connection c = DBUtil.getConnection();
            PreparedStatement ps = c.prepareStatement(sql)) {

            ps.setInt(1,start);
            ps.setInt(2,count);

            ResultSet rs = ps.executeQuery();

            while (rs.next())
            {
                Record record = new Record();
                record.id = rs.getInt(1);
                record.spend  = rs.getInt(2);
                record.cid = rs.getInt(3);
                record.comment = rs.getString(4);
                record.date = rs.getDate(5);

                configList.add(record);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } ;

        return configList;
    }

    public List<Record> list(int cid) {
        List<Record> records = new ArrayList<>();

        String sql = "select * from record where cid=?";

        try(Connection c = DBUtil.getConnection();
        PreparedStatement ps = c.prepareStatement(sql)) {

            ps.setInt(1,cid);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Record record = new Record();
                int id = rs.getInt("id");
                int spend = rs.getInt("spend");

                String comment = rs.getString("comment");
                Date date = rs.getDate("date");

                record.spend=spend;
                record.cid=cid;
                record.comment=comment;
                record.date=date;
                record.id = id;
                records.add(record);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return records;
    }

    public List<Record> listToday() {
        return list(DateUtil.Today());
    }

    public List<Record> list(java.util.Date date) {
        List<Record> recordList = new ArrayList<>();
        String sql = "select * from record where date=?";

        try (Connection c = DBUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)){

            ps.setDate(1,DateUtil.util2sql(date));
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Record record = new Record();
                int id = rs.getInt("id");
                int cid = rs.getInt("cid");
                int spend = rs.getInt("spend");

                String comment = rs.getString("comment");
                Date date1 = rs.getDate("date");

                record.spend=spend;
                record.cid=cid;
                record.comment=comment;
                record.date=date1;
                record.id = id;
                recordList.add(record);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return recordList;
    }

    public List<Record> listThisMonth(){
        return list(DateUtil.monthBegin(),DateUtil.monthEnd());
    }

    public List<Record> list(java.util.Date start, java.util.Date end) {
        List<Record> records = new ArrayList<Record>();
        String sql = "select * from record where date >=? and date <= ?";
        try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setDate(1, DateUtil.util2sql(start));
            ps.setDate(2, DateUtil.util2sql(end));
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Record record = new Record();
                int id = rs.getInt("id");
                int cid = rs.getInt("cid");
                int spend = rs.getInt("spend");

                String comment = rs.getString("comment");
                Date date = rs.getDate("date");

                record.spend=spend;
                record.cid=cid;
                record.comment=comment;
                record.date=date;
                record.id = id;
                records.add(record);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return records;
    }

    public int getTotal() {
        int total = 0;


        try(Connection c = DBUtil.getConnection();
            Statement ps = c.createStatement();) {

            String sql = "select count(*) from record";

            ResultSet rs = ps.executeQuery(sql);
//            if (rs.next()) {//这里站长用的while
//                total = rs.getInt(1);
//            }
            while (rs.next()) {
                total = rs.getInt(1);
            }

            System.out.println("total:" + total);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return total;
    }
}
